<?php
/***************************************************************
*  Copyright notice
*
*  (c) 2009 Christian Kartnig <office@hahnepeter.de>
*  All rights reserved
*
*  This script is part of the TYPO3 project. The TYPO3 project is
*  free software; you can redistribute it and/or modify
*  it under the terms of the GNU General Public License as published by
*  the Free Software Foundation; either version 2 of the License, or
*  (at your option) any later version.
*
*  The GNU General Public License can be found at
*  http://www.gnu.org/copyleft/gpl.html.
*
*  This script is distributed in the hope that it will be useful,
*  but WITHOUT ANY WARRANTY; without even the implied warranty of
*  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*  GNU General Public License for more details.
*
*  This copyright notice MUST APPEAR in all copies of the script!
***************************************************************/


require_once(t3lib_extMgm::extPath('topstip').'lib/class.topstip_pibase.php');
require_once(t3lib_extMgm::extPath('topstip').'pi4/class.pdf_export.php');

/**
 * @author	Christian <office@hahnepeter.de>
 * @package	TYPO3
 * @subpackage	tx_test
 */
class tx_topstip_pi4 extends topstip_pibase {
	var $prefixId      = 'tx_topstip_pi4';		// Same as class name
	var $scriptRelPath = 'pi4/class.tx_topstip_pi4.php';	// Path to this script relative to the extension dir.
	var $extKey        = 'topstip';	// The extension key.
	var $pi_checkCHash = true;

	
	/**
	 * The main method of the PlugIn
	 *
	 * @param	string		$content: The PlugIn content
	 * @param	array		$conf: The PlugIn configuration
	 * @return	The content that is displayed on the website
	 */
	
	function main($content, $conf) {
		
		$this->conf = $conf;
		$this->pi_setPiVarDefaults();
		//$this->pi_loadLL();
		$this->pi_initPIflexForm();
		
		// the next line should not be necessary- but it is...why?
		$this->piVars = t3lib_div::GPvar($this->prefixId);

		
		if($this->piVars['export1']){
			$this->msg1 .= $this->exportList("buchungsliste");
		}
		if($this->piVars['export2']){
			$this->msg2 .= $this->exportList("serienbrief");
		}
		
		if($this->piVars['exportUid'] && $this->piVars['exportType']){
			
			switch($this->piVars['exportType']){
				case 'buchungsliste':
					$this->renderPDF(intval($this->piVars['exportUid']));
					break;
				case 'serienbrief':
					$this->renderCSV(intval($this->piVars['exportUid']),$this->piVars['tableKey']);
					break;
			}
		}
		
		$this->oForm = t3lib_div::makeInstance("tx_ameosformidable");
		$this->oForm->init(
			$this,	// reference to the current plugin object
			"EXT:topstip/pi4/xml/exportview.xml" // soft-path to xml file
		);
		
		$content .= $this->oForm->render();

		
		return $this->pi_wrapInBaseClass($content);
	}

	private function renderCSV($exportUid,$tableKey){
		$rows = $this->db->exec_SELECTgetRows('*', 'tx_kbks_serienbrief_export', 'deleted=0 AND uid='.$exportUid);
		$exportFields = 'tx_kbks_vorname,tx_kbks_nachname,tx_kbks_plz,tx_kbks_ort,tx_kbks_strasse,tx_kbks_ergebnis';
		$exportFieldnames = str_replace('tx_kbks_', '"', $exportFields);
		$exportFieldnames = str_replace(',', '";', $exportFieldnames).'"';
		if(count($rows)){
			$export = $rows[0];
			$antraege = unserialize($export['tx_kbks_antraege']);
			$antraegeCsv = $exportFieldnames."\n";
			// just select the anträge from the current antragstyp
			$antraege = $antraege[$tableKey];
			if(is_array($antraege)){
				foreach($antraege as $uid){
					$rows = $this->db->exec_SELECTgetRows($exportFields, $this->getTableName($tableKey), 'uid='.$uid.' AND deleted=0');
					if($rows[0]['tx_kbks_ergebnis']==1) {
						$rows[0]['tx_kbks_ergebnis'] = 'abgelehnt';
					} elseif($rows[0]['tx_kbks_ergebnis']==2) {
						$rows[0]['tx_kbks_ergebnis'] = 'angenommen';					
					}
					$antraegeCsv .= utf8_decode(t3lib_div::csvValues($rows[0], ';')."\n");
				}
			}
			$tableTitle = $this->getTableTitle($tableKey);
			header("Content-type: application/octet-stream");
			header("Content-Disposition: attachment; filename=\"".$tableTitle."_".$export['uid'].".csv\"");
			echo($antraegeCsv);
			die();
		}
	}

	private function renderPDF($exportUid){
		$rows = $this->db->exec_SELECTgetRows('*', 'tx_kbks_buchungsliste_export', 'deleted=0 AND uid='.$exportUid);
		$exportFields = 'tx_kbks_nachname,tx_kbks_vorname,tx_kbks_bank, tx_kbks_bank_andere, tx_kbks_blz, tx_kbks_kontonummer, tx_kbks_foerderhoehe';
		$where = "deleted=0";
		$orderBy = "tx_kbks_nachname";
		
		if(count($rows)){
			$export = $rows[0];
			$antraege = unserialize($export['tx_kbks_antraege']);
			
			$antraegeArr = array();
			$queryUnions = array();
			foreach($antraege as $tableKey => $uids){
				$tableName = $this->getTableName($tableKey);
				$tableTitle = $this->getTableTitle($tableKey);
				$queryUnions[] = "(SELECT $exportFields, '$tableKey' AS tableKey, '$tableTitle' AS tableTitle FROM $tableName WHERE $where AND uid IN (".implode(',',$uids)."))";
			}
			$query = "SELECT * FROM (".implode('UNION', $queryUnions).") unionTable ORDER BY ".$orderBy;
			
			$res = $this->db->sql_query($query);
			$bankArray = $this->getBankArray();
			
			while($row = $this->db->sql_fetch_assoc($res)){
				if(intval($row['tx_kbks_bank'])>0) {
					$bankId = $row['tx_kbks_bank'];
					$bank = $bankArray[$bankId]['tx_kbks_titel'];
					$blz = $bankArray[$bankId]['tx_kbks_blz'];
				} else {
					$bank = $row['tx_kbks_bank_andere'];
					$blz = $row['tx_kbks_blz'];
				} 
				$antraegeArr[] = array('Nachname' => utf8_decode($row['tx_kbks_nachname']), 
															 'Vorname' => utf8_decode($row['tx_kbks_vorname']), 
															 'BLZ' => utf8_decode($blz),
															 'Bank' => utf8_decode($bank), 
															 'Kontonummer' => utf8_decode($row['tx_kbks_kontonummer']), 
															 'Betrag' => utf8_decode($row['tx_kbks_foerderhoehe']), 
															 'Verwendungszweck' => utf8_decode($row['tableTitle']));
			}
			$pdfExport = new pdf_export();
			$pdfExport->setHeaderString("Buchungslisten Export vom ".date('d. F Y',$export['tx_kbks_export_datum']));
			$pdfExport->AliasNbPages("{nb}");
			$pdfExport->AddPage();
			$pdfExport->printRecords($antraegeArr);
			header("Content-type: application/pdf");
			$pdfExport->output();
			die();
			
		}
	}

	/**
	 * creates a new export record in one of the two tables "tx_kbks_buchungsliste_export" or "tx_kbks_serienbrief_export"
	 * @param string $table [buchungsliste|serienbrief]
	 * @return string the status message
	 */
	private function exportList($table){
		$otherTable = ($table=='buchungsliste') ? 'serienbrief' : 'buchungsliste';
		$content = "";
		$selectFields = "	tx_kbks_vorname,
											tx_kbks_nachname,
											tx_kbks_buchungsliste_exportiert, 
											tx_kbks_serienbrief_exportiert
											";
		$where ="	t.deleted=0 AND
							tx_kbks_".$table."_exportiert=0 AND 
							tx_kbks_status >5";
		$query = $this->getQuery($selectFields, $where);
		$res = $this->db->sql_query($query);
		
		$antraege = array();
		$i=0;
		while($row = $this->db->sql_fetch_assoc($res)){
			// add table and uid to export array
			$tableKey = $row['tableKey'];
			if(!isset($antraege[$tableKey])) {
				$antraege[$tableKey] = array();
			}
			$antraege[$tableKey][] = $row['uid'];

			// update antrag record with flags, and if both flags are set, with status
			$updateData = array('tx_kbks_'.$table.'_exportiert'=>1);
			if($row['tx_kbks_'.$otherTable.'_exportiert']==1) {
				if($row['tableName']=='tx_kbks_topstip_ausland' || $row['tableName']=='tx_kbks_topstip_diplom') {
					$updateData['tx_kbks_status'] = 8;
				} else {
					$updateData['tx_kbks_status'] = 7;
				}
			}
			
			$tableName = $this->getTableName($row['tableKey']);
			$this->db->exec_UPDATEquery($tableName, 'uid='.$row['uid'], $updateData);
			$i++;
		}
		if($i){
			// if antraege found, write serialized array in export table
			$insertData = array(
							'pid'=>$this->conf['pidExports'],
							'tx_kbks_export_datum'=>time(),
							'tx_kbks_antraege'=>serialize($antraege),
							'crdate'=>time(),
							);
			$ok = $this->db->exec_INSERTquery('tx_kbks_'.$table.'_export', $insertData);
			$content = $i." Antr&auml;ge wurden erfolgreich exportiert.";
			
			
		}else{
			$content = "keine Einträge für den Export gefunden!";
		}
		
		return $content;
		
	}

	public function getSerienbriefExportLinks($dataRow){
		$html = "";
		$antraege = unserialize($dataRow['tx_kbks_antraege']);
		foreach($antraege as $tableKey => $arr){
			if(count($arr)){
				$getParams = array( 'tx_topstip_pi4[exportUid]' => $dataRow['uid'],
									'tx_topstip_pi4[exportType]' => 'serienbrief',
									'tx_topstip_pi4[tableKey]' => $tableKey
								);

				$url = $this->pi_getPageLink($this->tsfe->id, '_self', $getParams);
				$html .= '<a href="'.$url.'">Download '.$this->getTableTitle($tableKey).'</a><br/>';
			}
		}
		return $html;
	}
	
	public function getSerienbriefExportSql(){
		$query = "SELECT uid, tx_kbks_export_datum, tx_kbks_antraege
					FROM tx_kbks_serienbrief_export
					WHERE deleted = 0
					ORDER BY uid DESC";

		return $query;
	}

	public function getBuchungslisteExportSql(){
		$query = "SELECT uid, tx_kbks_export_datum, tx_kbks_antraege
					FROM tx_kbks_buchungsliste_export
					WHERE deleted = 0
					ORDER BY uid DESC";

		return $query;
	}
	
	
}

if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/topstip/pi4/class.tx_test_pi4.php'])	{
	include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/topstip/pi4/class.tx_test_pi4.php']);
}

?>
